using System;
using System.Collections.Generic;
using System.Text;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Text.RegularExpressions;


namespace SqlBlocks.Drivers
{
    public delegate void SqlCommandRunner(SqlCommand command);
    public static class SqlServerHelper
    {
        /// <summary>
        /// Cleans management studio cruft from raw SQL.
        ///  * Go statements are removed
        ///  * USE [database] statements are removed
        /// </summary>
        /// <param name="sql">SQL Management Studio generated text</param>
        /// <returns>SQL we can run programatically</returns>
        public static string[] Clean(string sql)
        {
            if (sql == null)
            {
                throw new ArgumentNullException("sql");
            }

            //var patterns = new string[] 
            //{ 
            //    @"(^\s*)USE\ (.*)(\s*$)", // Nuke USE [Database] statements
            //    @"(^\s*)GO(\s*$)" // Get rid of "GO" lines
            //};
            var regex = new Regex(
                @"(^\s*)USE\ (.*)(\s*$)",
                RegexOptions.Multiline | RegexOptions.CultureInvariant
                );

            sql = regex.Replace(sql, "$1$+");

            regex = new Regex(
                    @"(^\s*)GO(\s*$)",
                    RegexOptions.Multiline | RegexOptions.CultureInvariant
                    );

            var blocks = regex.Split(sql);



            return blocks;
        }
        public static string ScriptDatabase(string cn)
        {
            throw new NotImplementedException("The script database function has not yet been built");

            //System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(cn);
            //Server srvr = new Server(new ServerConnection(conn));
            //Microsoft.SqlServer.Management.Smo.Database db = srvr.Databases[conn.Database];

            //// Setup transfer
            //Transfer t = new Transfer(db);
            //t.CopyAllObjects = true;
            //t.CopySchema = true;
            //t.DestinationServer = conn.DataSource;
            //t.DestinationDatabase = conn.Database + "_temp";
            //t.Options.IncludeIfNotExists = true;
            //t.Options.ContinueScriptingOnError = true;
            //t.CreateTargetDatabase = false;
            //t.CreateTargetDatabase = true;

            //// Capture Transfer
            //StringCollection commands = t.ScriptTransfer();


            //// Get Script Into A String
            //StringBuilder sb = new StringBuilder();
            //foreach (string s in commands)
            //    sb.Append(s);
            //string script = sb.ToString();


            //srvr = null;
            //return script;
        }

        #region Query runners
        /// <summary>
        /// Runs a single query against the specified connection
        /// </summary>
        /// <param name="cn">Target database connection string</param>
        /// <param name="query">The query to execute</param>
        /// <param name="useTransaction">Indicates whether the entire statement should be run within a transaction</param>
        public static void ExecuteNonQuery(string cn, string query, bool useTransaction)
        {
            ExecuteNonQuery(cn, new string[] { query }, useTransaction);
        }
        /// <summary>
        /// Runs a query set against the specified connection, optionally wrapping the entire set in a single transaction
        /// </summary>
        /// <param name="cn">Target database connection string</param>
        /// <param name="querySet">A list of statements to execute</param>
        /// <param name="useTransaction">Indicates whether the entire set should be wrapped in a single transaction</param>
        public static void ExecuteNonQuery(string connectionString, IEnumerable<string> querySet, bool useTransaction)
        {
            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                cn.Open();
                SqlTransaction transaction = (useTransaction) ? cn.BeginTransaction() : null;
                foreach (string s in querySet)
                {
                    var sql = s != null ? s.Trim() : null;
                    if (string.IsNullOrEmpty(sql)) continue;
                    using (SqlCommand cmd = new SqlCommand(sql, cn, transaction))
                    {
                        RunSqlCommand(cmd, command => command.ExecuteNonQuery());
                    }
                }

                if (transaction != null)
                {
                    transaction.Commit();
                }
            }
        }

        public static object ExecuteScalar(SqlCommand cmd, string connectionString, bool useTransaction)
        {
            cmd.Connection = new SqlConnection(connectionString);

            return ExecuteScalar(cmd, useTransaction);
        }

        public static object ExecuteScalar(SqlCommand cmd, bool useTransaction)
        {
            object scalar = null;
            RunSqlCommand(
                cmd, 
                useTransaction, 
                command => scalar = command.ExecuteScalar()
                );
            return scalar;
        }

        public static object ExecuteScalar(string connectionString, string query, bool useTransaction)
        {
            object scalar = null;
            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                RunSqlCommand(query, cn, useTransaction, 
                    delegate(SqlCommand cmd)
                    {
                        scalar = cmd.ExecuteScalar();
                    }
                );
            }
            return scalar;
        }


        static void RunSqlCommand(string command, SqlConnection connection, bool useTransaction, SqlCommandRunner execute)
        {
            using (SqlCommand cmd = new SqlCommand(command, connection))
            {
                RunSqlCommand(cmd, useTransaction, execute);
            }      
        }

        static void RunSqlCommand(SqlCommand cmd, bool useTransaction, SqlCommandRunner execute)
        {
            OpenConnection(cmd.Connection);
            SqlTransaction transaction = 
                cmd.Transaction = 
                    (useTransaction) ? cmd.Connection.BeginTransaction() : null;
            
            RunSqlCommand(cmd, execute);

            if (transaction != null)
            {
                transaction.Commit();
            }
        }


        static void OpenConnection(SqlConnection connection)
        {
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
            }
        }

        static void RunSqlCommand(SqlCommand cmd, SqlCommandRunner execute)
        {
            SqlTransaction transaction = cmd.Transaction;
            try
            {
                execute(cmd);
            }
            catch (SqlException x)
            {
                //if there's an error and a transaction, roll everything back
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw x;
            }
        }

        #endregion

        #region Table translation

        //public static string GetColumnDefinition(Column column)
        //{
        //    SettingsCollection settings = column.GetSettings();
        //    StringBuilder col = new StringBuilder();
        //    col.Append("[");
        //    col.Append(column.Name);
        //    col.Append("] ");
        //    col.Append(GetColumnType(column));
        //    if (settings.GetTypedValue<bool>("primary_key", false))
        //    {
        //        col.Append(" PRIMARY KEY ");
        //    }
        //    else
        //    {
        //        col.Append(settings.GetTypedValue<bool>("nullable", true) ? " NULL " : " NOT NULL ");
        //    }

        //    return col.ToString();
        //}
        //public static string GetColumnType(Column column)
        //{
        //    string def = "";
        //    SettingsCollection settings = column.GetSettings();
        //    Type type = settings.GetTypedValue("type", typeof(string));
        //    if (type == typeof(int))
        //    {
        //        def = "int";
        //    }
        //    else if (type == typeof(string))
        //    {
        //        int length = settings.GetTypedValue("length", 255);
        //        bool fixedLength = settings.GetTypedValue("fixedwidth", false);
        //        if (fixedLength)
        //        {
        //            def = string.Format("nchar({0})", length > 0 ? length : 1);
        //        }
        //        else
        //        {
        //            def = string.Format("nvarchar({0})", length == -1 ? "MAX" : length.ToString());
        //        }
        //    }
        //    else if (type == typeof(double))
        //    {
        //        def = "float";
        //    }
        //    else if (type == typeof(bool))
        //    {
        //        def = "bit";
        //    }
        //    else if (type == typeof(DateTime))
        //    {
        //        def = "datetime";
        //    }
        //    else if (type == typeof(Guid))
        //    {
        //        def = "uniqueidentifier";
        //    }

        //    return def;
        //    //if (type == typeof(string))
        //    //{
        //    //    if (size <= 255)
        //    //        return string.Format("varchar({0})", size);
        //    //    else
        //    //        return "text";
        //    //}
        //    //else if (type == typeof(int))
        //    //{
        //    //    if (size >= 8)
        //    //        return "bigint";
        //    //    else
        //    //        return "int";
        //    //}
        //    //else if (type == typeof(float) || type == typeof(double))
        //    //{
        //    //    if (size == 0)
        //    //        return "real";
        //    //    else
        //    //        return string.Format("float({0})", size);
        //    //}
        //    //else if (type == typeof(bool))
        //    //{
        //    //    return "bit";
        //    //}
        //    //else if (type == typeof(DateTime))
        //    //{
        //    //    return "datetime";
        //    //}
        //    //else if (type == typeof(char))
        //    //{
        //    //    return string.Format("char({0})", size);
        //    //}
        //    //else if (type == typeof(Guid))
        //    //{
        //    //    return "uniqueidentifier";
        //    //}
        //    //else
        //    //{
        //    //    throw new NotSupportedException("Type not supported : " + type.Name);
        //    //}
        //}
        #endregion
    }
}
